using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

using Peradigm.Framework.Domain.Exceptions;
using Peradigm.Framework.Domain.Parties;
using Peradigm.Framework.Domain.Security;
using Peradigm.Framework.OrganizationalHierarchyService;
using Peradigm.Framework.Persistence;
using Peradigm.Framework.Persistence.Exceptions;

using Liberty.Domain.CaseManagement;
using Liberty.Domain.GrantManagement;
using Liberty.Domain.ServiceMemberManagement;
using Liberty.Persistence.GrantManagement;
using Liberty.Persistence.Kernel;
using Liberty.Persistence.Security;

namespace Liberty.Persistence.CaseManagement
{
    public class CaseBroker : AbstractBroker
    {
        #region Constants

        private const string
            CASE_WITH = "CaseManagement.CaseWith",
            CASES_FOR_SERVICE_MEMBER = "CaseManagement.CasesForServiceMember",
            INJURIES_FOR_CASE = "CaseManagement.InjuriesForCase",
            VISITS_FOR_CASE = "CaseManagement.VisitsForCase",
            SAVE_CASE = "CaseManagement.SaveCase",
            SAVE_INJURY = "CaseManagement.SaveInjury",
            SAVE_VISIT = "CaseManagement.SaveVisit",
            SP_GET_ALL_INJURY_TYPES = "[CaseManagement].[GetAllTypesOfInjury]";

        private const string FIELD_PREFIX = "@";

        private const string
            FLD_OID = "CaseOID",
            FLD_VERSION = "CaseVersion",
            FLD_SERVICEMAN_ID = "ServiceManID",
            FLD_CASE_VOLUNTEER_ID = "CaseVolunteerID",
            FLD_CASE_DIRECTOR_ID = "CaseDirectorID",
            FLD_USER_OID = "UsersOID",

            FLD_INJURY_OID = "InjuryOID",
            FLD_INJURY_VERSION = "InjuryVersion",
            FLD_INJURY_DATE = "InjuryDate",
            FLD_INJURY_UPDATED_DATE = "InjuryUpdatedDate",
            FLD_INJURY_CREATED_DATE = "InjuryCreatedDate",
            FLD_INJURY_ID = "InjuryID",
            FLD_INJURY_DESCRIPTION = "InjuryDescription",

            FLD_VISIT_OID = "VisitOID",
            FLD_VISIT_VERSION = "VisitVersion",
            FLD_VISIT_ARRIVAL_DATE = "VisitArrivalDate",
            FLD_VISIT_DISCHARGE_DATE = "VisitDischargeDate",
            FLD_VISIT_FACILITY_ID = "VisitFacilityID",
            FLD_VISIT_FAMILY_PRESENT = "VisitFamilyPresent",
            FLD_VISIT_FAMILY_ARRIVAL_DATE = "VisitFamilyArrivalDate",
            FLD_VISIT_FAMILY_DEPARTURE_DATE = "VisitFamilyDepartureDate",
            FLD_VISIT_METHOD_OF_TRAVEL = "VisitMethodOfTravel",
            FLD_VISIT_ORIGIN_OF_TRAVEL = "VisitOriginOfTravel",
            FLD_VISIT_INVITATIONAL_TRAVEL_ORDERS = "VisitInvitationalTravelOrders";

        private const string CASE_CONTEXT_OID = "Case.OID";
        private const string ERROR_SAVE_CASE = "Unable to save case.";

        #endregion

        #region Event Handlers
        #endregion

        #region Methods
#warning Missing logic to add loaded cases to serviceMember
#warning This method does not return the grants associate with each case as that would cause infinite recursion.
        public List<Case> CasesFor( ServiceMember serviceMember )
        {
            List<Case> cases = new List<Case>();
            SafeReader reader = null;
            SqlCommand cmd = null;

            try
            {
                cmd = this.CommandFor( CASES_FOR_SERVICE_MEMBER );

                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_SERVICEMAN_ID, serviceMember.Oid ) );

                reader = this.ExecuteReader( cmd );
                while( reader.Read() )
                {
                    Case myCase = this.CaseFrom( reader );
                    cases.Add( myCase );
                }
            }
            finally
            {
                this.Close( cmd );
                this.Close( reader );
            }

            return cases;
        }

        public Case CaseWith( long caseID )
        {
            SqlCommand cmd = null;
            SafeReader reader = null;
            Case myCase = null;

            try
            {
                cmd = this.CommandFor( CASE_WITH );

                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_OID, caseID ) );

                reader = this.ExecuteReader( cmd );
                if( reader.Read() )
                {
                    myCase = this.CaseFrom( reader );
                }
            }
            catch( Exception )
            {
                throw;
            }
            finally
            {
                this.Close( cmd );
                this.Close( reader );
            }

            return myCase;
        }

        /// <summary>
        /// Answer a list of Injury reference values
        /// </summary>
        /// <returns></returns>
        public List<TypeOfInjury> InjuryTypes()
        {
            ReferenceValueBroker<TypeOfInjury> broker = new ReferenceValueBroker<TypeOfInjury>( this.ConnectionString, SP_GET_ALL_INJURY_TYPES );
            return broker.Values();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="oid"></param>
        /// <returns></returns>
        public TypeOfInjury TypeOfInjuryWith( long oid )
        {
            ReferenceValueBroker<TypeOfInjury> broker = new ReferenceValueBroker<TypeOfInjury>( this.ConnectionString, SP_GET_ALL_INJURY_TYPES );
            return broker.ValueWith( oid );
        }

        public Case Save( Case myCase )
        {
            SqlTransaction txn = this.NewTransaction();
            CaseBroker caseBroker;

            try
            {
                caseBroker = new CaseBroker( txn );
                caseBroker.SaveCase( myCase );

                this.Commit( txn );
            }
            catch( EnterpriseException ex )
            {
                this.Rollback( txn );
                ex.AddContextItem( CASE_CONTEXT_OID, myCase.Oid.ToString() );
                throw ex;
            }
            catch( Exception e )
            {
                this.Rollback( txn );
                ObjectNotSavedException onse = new ObjectNotSavedException( ERROR_SAVE_CASE, e );
                if( myCase != null )
                {
                    onse.AddContextItem( CASE_CONTEXT_OID, myCase.Oid.ToString() );
                }
                throw onse;
            }

            return myCase;
        }

        #endregion

        #region Properties
        #endregion

        #region Private Methods

        private static DateTime? ReadDateTime( SafeReader reader, string fieldName )
        {
            if( reader.IsDBNull( fieldName ) )
            {
                return null;
            }
            else
            {
                return reader.GetDateTime( fieldName );
            }
        }

        internal Case CaseFrom( SafeReader reader )
        {
            long oid = reader.GetInt64( FLD_OID );
            byte[] version = reader.GetRowVersion( FLD_VERSION );

            long serviceManID = reader.GetInt64( FLD_SERVICEMAN_ID );
            ServiceMember serviceMember = this.i_PersonBroker.ServiceMemberWith( serviceManID );

#warning Cannot load grants for the case because that would create a recursive situation.
            // List<Grant> grants = this.i_GrantBroker.GrantsForCase( oid );
            UserBroker userBroker = new UserBroker( this.ConnectionString );
            User user = userBroker.UserWith( reader.GetInt64( FLD_USER_OID ) );
            CaseVolunteer volunteer = new CaseVolunteer( user.Oid, user.FirstName, user.LastName );
            volunteer.Guid = user.Guid;
            volunteer.UPN = user.UPN;

            Case myCase = new Case( oid, version, new List<Injury>(), serviceMember, volunteer );
            myCase.DateOfInjury = ReadDateTime( reader, FLD_INJURY_DATE );

            this.InjuriesFor( myCase );
            this.VisitsFor( myCase );

            return myCase;
        }

        private List<Case> CasesFrom( SqlCommand cmd )
        {
            SafeReader reader;
            List<Case> cases;

            reader = null;
            try
            {
                reader = this.ExecuteReader( cmd );
                cases = new List<Case>();
                while( reader.Read() )
                {
                    cases.Add( this.CaseFrom( reader ) );
                }
            }
            finally
            {
                if( reader != null )
                {
                    this.Close( reader );
                    reader = null;
                }
            }

            return cases;
        }

        private Case SaveCase( Case myCase )
        {
            SqlCommand cmd = null;
            SafeReader reader = null;

            try
            {
                cmd = this.CommandFor( SAVE_CASE );

                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_OID, myCase.Oid ) );
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_VERSION, myCase.Version ) );

                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_SERVICEMAN_ID, myCase.ServiceMember.Oid ) );
                if( myCase.Volunteer != null )
                {
                    cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_CASE_VOLUNTEER_ID, myCase.Volunteer.Oid ) );
                }
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_INJURY_DATE, myCase.DateOfInjury ) );

                /*
                 * CaseDirectorID
                 * Grants
                 */

                // Execute and read oid/timestamp
                reader = this.ExecuteReader( cmd );
                if( reader.Read() )
                {
                    myCase.Oid = reader.GetInt64( FLD_OID );
                    myCase.Version = reader.GetRowVersion( FLD_VERSION );
                }
                else
                {
                    throw new ObjectNotSavedException( "Unable to save case" );
                }

                foreach( Injury injury in myCase.Injuries )
                {
                    this.SaveInjuryIn( injury, myCase );
                }

                foreach( Visit visit in myCase.Visits )
                {
                    this.SaveVisitIn( visit, myCase );
                }
            }
            finally
            {
                this.Close( reader );
                this.Close( cmd );
            }

            return myCase;
        }

        private Injury SaveInjuryIn( Injury injury, Case aCase )
        {
            SqlCommand cmd = null;
            SafeReader reader = null;

            try
            {
                cmd = this.CommandFor( SAVE_INJURY );

                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_INJURY_OID, injury.Oid ) );
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_INJURY_VERSION, injury.Version ) );

                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_OID, aCase.Oid ) );
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_INJURY_ID, injury.TypeOfInjury.Oid ) );

                // Execute and read oid/timestamp
                reader = this.ExecuteReader( cmd );
                if( reader.Read() )
                {
                    injury.Oid = reader.GetInt64( FLD_INJURY_OID );
                    injury.Version = reader.GetRowVersion( FLD_INJURY_VERSION );
                }
                else
                {
                    throw new ObjectNotSavedException( "Unable to save injury" );
                }
            }
            finally
            {
                this.Close( reader );
                this.Close( cmd );
            }

            return injury;
        }

        private Visit SaveVisitIn( Visit visit, Case aCase )
        {
            SqlCommand cmd = null;
            SafeReader reader = null;

            try
            {
                cmd = this.CommandFor( SAVE_VISIT );

                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_VISIT_OID, visit.Oid ) );
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_VISIT_VERSION, visit.Version ) );

                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_OID, aCase.Oid ) );
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_VISIT_ARRIVAL_DATE, visit.ArrivalDate ) );
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_VISIT_DISCHARGE_DATE, visit.DischargeDate ) );
                long facilityID = Convert.ToInt64( visit.Facility.Code );
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_VISIT_FACILITY_ID, facilityID ) );
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_VISIT_FAMILY_PRESENT, visit.FamilyIsPresent ) );
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_VISIT_FAMILY_ARRIVAL_DATE, visit.FamilyArrival ) );
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_VISIT_FAMILY_DEPARTURE_DATE, visit.FamilyDepartureOn ) );
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_VISIT_METHOD_OF_TRAVEL, visit.MethodOfTravel ) );
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_VISIT_ORIGIN_OF_TRAVEL, visit.OriginOfTravel ) );
                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_VISIT_INVITATIONAL_TRAVEL_ORDERS, visit.HasInvitationalTravelOrders ) );
#warning Add save visit parameters.

                // Execute and read oid/timestamp
                reader = this.ExecuteReader( cmd );
                if( reader.Read() )
                {
                    visit.Oid = reader.GetInt64( FLD_VISIT_OID );
                    visit.Version = reader.GetRowVersion( FLD_VISIT_VERSION );
                }
                else
                {
                    throw new ObjectNotSavedException( "Unable to save visit" );
                }
            }
            finally
            {
                this.Close( reader );
                this.Close( cmd );
            }

            return visit;
        }

        private void InjuriesFor( Case myCase )
        {
            List<Injury> injuries = new List<Injury>();
            SafeReader reader = null;
            SqlCommand cmd = null;

            try
            {
                cmd = this.CommandFor( INJURIES_FOR_CASE );

                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_OID, myCase.Oid ) );

                reader = this.ExecuteReader( cmd );
                for( int injuryRecord = 0; ( injuryRecord < 3 ) && reader.Read(); injuryRecord++ )
                {
                    Injury injury = new Injury( reader.GetInt64( FLD_INJURY_OID ),
                                                reader.GetRowVersion( FLD_INJURY_VERSION ),
                                                TypeOfInjuryWith( reader.GetInt64( FLD_INJURY_ID ) ) );
                    myCase.AddInjury( injury );
                }
            }
            finally
            {
                this.Close( cmd );
                this.Close( reader );
            }
        }

        private void VisitsFor( Case myCase )
        {
            List<Visit> visits = new List<Visit>();
            SafeReader reader = null;
            SqlCommand cmd = null;

            try
            {
                OrganizationalUnit hierarchy = OrganizationalService.Service.GetOrganizationalHierarchy();

                cmd = this.CommandFor( VISITS_FOR_CASE );

                cmd.Parameters.Add( new SqlParameter( FIELD_PREFIX + FLD_OID, myCase.Oid ) );

                reader = this.ExecuteReader( cmd );
                while( reader.Read() )
                {
                    long facilityID = reader.GetInt64( FLD_VISIT_FACILITY_ID );
                    Facility facility = (Facility)hierarchy.ChildUnitWith( facilityID.ToString() );

                    Visit visit = new Visit( reader.GetInt64( FLD_VISIT_OID ),
                                             reader.GetRowVersion( FLD_VISIT_VERSION ),
                                             facility,
                                             reader.GetBoolean( FLD_VISIT_FAMILY_PRESENT ) );
#warning Add other visit properties
                    visit.ArrivalDate = ReadDateTime( reader, FLD_VISIT_ARRIVAL_DATE );
                    visit.DischargeDate = ReadDateTime( reader, FLD_VISIT_DISCHARGE_DATE );
                    visit.FamilyArrival = ReadDateTime( reader, FLD_VISIT_FAMILY_ARRIVAL_DATE );
                    visit.FamilyDepartureOn = ReadDateTime( reader, FLD_VISIT_FAMILY_DEPARTURE_DATE );
                    visit.MethodOfTravel = reader.GetString( FLD_VISIT_METHOD_OF_TRAVEL );
                    visit.OriginOfTravel = reader.GetString( FLD_VISIT_ORIGIN_OF_TRAVEL );
                    visit.HasInvitationalTravelOrders = reader.GetBoolean( FLD_VISIT_INVITATIONAL_TRAVEL_ORDERS );
                    myCase.AddVisit( visit );
                }
            }
            finally
            {
                this.Close( cmd );
                this.Close( reader );
            }
        }

        #endregion

        #region Private Properties
        #endregion

        #region Construction and Finalization

        public CaseBroker( SqlTransaction txn )
            : base( txn )
        {
            i_PersonBroker = new PersonBroker( txn );
        }

        public CaseBroker( string cxnString )
            : base( cxnString )
        {
            i_PersonBroker = new PersonBroker( cxnString );
        }

        #endregion

        #region Public Methods

        #endregion

        #region Data Elements
        PersonBroker i_PersonBroker = null;
        #endregion
    }
}
